CREATE TABLE [dbo].[tb_Residencias]
(
[idResidencia] [int] NOT NULL IDENTITY(1, 1),
[Agente] [int] NOT NULL,
[Desde] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Hasta] [datetime] NULL,
[Localidad] [int] NOT NULL,
[FechaPresentacion] [datetime] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[AsignarFechaDefaultResidencias] ON [dbo].[tb_Residencias] 
FOR INSERT, UPDATE
AS
BEGIN
--AsignarFechaDefault
SET NOCOUNT ON

DECLARE @FechaPresentacion as datetime
DECLARE @id as int


SELECT @FechaPresentacion = FechaPresentacion,@id=idResidencia  FROM INSERTED

	IF (@@ROWCOUNT=1) 
	 BEGIN
	    IF @FechaPresentacion IS NULL SELECT @FechaPresentacion= dbo.FECHA_PRESENTACION_DEFAULT ()
	    UPDATE tb_Residencias SET FechaPresentacion = @FechaPresentacion WHERE idResidencia=@id
	 END

END


GO
ALTER TABLE [dbo].[tb_Residencias] ADD CONSTRAINT [PK_tb_Residencias] PRIMARY KEY NONCLUSTERED  ([idResidencia]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Residencias] WITH NOCHECK ADD CONSTRAINT [FK_tb_Residencias_tb_Agentes] FOREIGN KEY ([Agente]) REFERENCES [dbo].[tb_Agentes] ([idAgente])
GO
ALTER TABLE [dbo].[tb_Residencias] WITH NOCHECK ADD CONSTRAINT [FK_tb_Residencias_tb_Localidades] FOREIGN KEY ([Localidad]) REFERENCES [dbo].[tb_Localidades] ([idLocalidad])
GO
